﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using MxWeiXinPF.DBUtility;//Please add references
namespace MxWeiXinPF.DAL
{
	/// <summary>
	/// 数据访问类:wx_shop_cart
	/// </summary>
    public partial class wx_shop_cart
    {
        public int Add(MxWeiXinPF.Model.wx_shop_cart model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into wx_shop_cart(");
            builder.Append("openid,wid,productId,skuId,skuInfo,totPrice,productNum,createDate,userid)");
            builder.Append(" values (");
            builder.Append("@openid,@wid,@productId,@skuId,@skuInfo,@totPrice,@productNum,@createDate,@userid)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@productId", SqlDbType.Int, 4), new SqlParameter("@skuId", SqlDbType.Int, 4), new SqlParameter("@skuInfo", SqlDbType.VarChar, 500), new SqlParameter("@totPrice", SqlDbType.Float, 8), new SqlParameter("@productNum", SqlDbType.Int, 4), new SqlParameter("@createDate", SqlDbType.DateTime), new SqlParameter("@userid", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.openid;
            cmdParms[1].Value = model.wid;
            cmdParms[2].Value = model.productId;
            cmdParms[3].Value = model.skuId;
            cmdParms[4].Value = model.skuInfo;
            cmdParms[5].Value = model.totPrice;
            cmdParms[6].Value = model.productNum;
            cmdParms[7].Value = model.createDate;
            cmdParms[8].Value = model.userid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public MxWeiXinPF.Model.wx_shop_cart DataRowToModel(DataRow row)
        {
            MxWeiXinPF.Model.wx_shop_cart _cart = new MxWeiXinPF.Model.wx_shop_cart();
            if (row != null)
            {
                if ((row["id"] != null) && (row["id"].ToString() != ""))
                {
                    _cart.id = int.Parse(row["id"].ToString());
                }
                if (row["openid"] != null)
                {
                    _cart.openid = row["openid"].ToString();
                }
                if ((row["wid"] != null) && (row["wid"].ToString() != ""))
                {
                    _cart.wid = int.Parse(row["wid"].ToString());
                }
                if ((row["productId"] != null) && (row["productId"].ToString() != ""))
                {
                    _cart.productId = int.Parse(row["productId"].ToString());
                }
                if ((row["skuId"] != null) && (row["skuId"].ToString() != ""))
                {
                    _cart.skuId = int.Parse(row["skuId"].ToString());
                }
                if (row["skuInfo"] != null)
                {
                    _cart.skuInfo = row["skuInfo"].ToString();
                }
                if ((row["totPrice"] != null) && (row["totPrice"].ToString() != ""))
                {
                    _cart.totPrice = decimal.Parse(row["totPrice"].ToString());
                }
                if ((row["productNum"] != null) && (row["productNum"].ToString() != ""))
                {
                    _cart.productNum = int.Parse(row["productNum"].ToString());
                }
                if ((row["createDate"] != null) && (row["createDate"].ToString() != ""))
                {
                    _cart.createDate = DateTime.Parse(row["createDate"].ToString());
                }
                if ((row["userid"] != null) && (row["userid"].ToString() != ""))
                {
                    _cart.userid = int.Parse(row["userid"].ToString());
                }
            }
            return _cart;
        }

        public bool Delete(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from wx_shop_cart ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string idlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from wx_shop_cart ");
            builder.Append(" where id in (" + idlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public bool Exists(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from wx_shop_cart");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetCartList(string openid, int wid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select c.id,c.openid,c.wid,c.productId,c.skuId,c.skuInfo,c.totPrice,c.productNum,c.createDate,pr.productName,pr.shortDesc,pr.pic,pr.stock,pr.salePrice,pr.marketPrice,pr.costPrice,pr.RatePrice*isnull(c.productNum,0) RatePrice,pr.shopid,c.userid FROM wx_shop_cart c \r\n                            left join (select  id,productName,shortDesc,shopid,salePrice,marketPrice,costPrice,stock,(select top 1 original_path from wx_shop_albums where productid=p.id) as pic ,case when (p.salePrice-p.costPrice)>0 then (p.salePrice-p.costPrice) else 0 end RatePrice from wx_shop_product p ) pr  on pr.id=c.productId   \r\n                            where c.openid=@openid and c.wid=@wid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@wid", SqlDbType.Int, 4) };
            cmdParms[0].Value = openid;
            cmdParms[1].Value = wid;
            return DbHelperSQL.Query(builder.ToString(), cmdParms);
        }

        public DataSet GetCartListByUserID(int userid, int wid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select c.id,c.openid,c.wid,c.productId,c.skuId,c.skuInfo,c.totPrice,c.productNum,c.createDate,pr.productName,pr.shortDesc,pr.pic,pr.stock,pr.RatePrice*isnull(c.productNum,0) RatePrice,pr.shopid,c.userid FROM wx_shop_cart c \r\n                            left join (select  id,productName,shortDesc,stock,shopid,(select top 1 original_path from wx_shop_albums where productid=p.id) as pic ,case when (p.salePrice-p.costPrice)>0 then (p.salePrice-p.costPrice) else 0 end RatePrice from wx_shop_product p ) pr  on pr.id=c.productId   \r\n                            where c.userid=@userid and c.wid=@wid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@userid", SqlDbType.Int, 4), new SqlParameter("@wid", SqlDbType.Int, 4) };
            cmdParms[0].Value = userid;
            cmdParms[1].Value = wid;
            return DbHelperSQL.Query(builder.ToString(), cmdParms);
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select id,openid,wid,productId,skuId,skuInfo,totPrice,productNum,createDate,userid,totPrice/productNum avgPrice ");
            builder.Append(" FROM wx_shop_cart ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" id,openid,wid,productId,skuId,skuInfo,totPrice,productNum,createDate,userid,,totPrice/productNum avgPrice ");
            builder.Append(" FROM wx_shop_cart ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.id desc");
            }
            builder.Append(")AS Row, T.*  from wx_shop_cart T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("id", "wx_shop_cart");
        }

        public MxWeiXinPF.Model.wx_shop_cart GetModel(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 id,openid,wid,productId,skuId,skuInfo,totPrice,productNum,createDate,userid from wx_shop_cart ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            MxWeiXinPF.Model.wx_shop_cart _cart = new MxWeiXinPF.Model.wx_shop_cart();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                return this.DataRowToModel(set.Tables[0].Rows[0]);
            }
            return null;
        }

        public DataSet GetProductList_SaleOrder(string openid, int wid, string productlistid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select id productId,wid,productName,marketPrice totPrice,salePrice,1 productNum,ShopID,case when (salePrice-costPrice)>0 then (salePrice-costPrice) else 0 end RatePrice,getdate() createDate,stock from wx_shop_product  ");
            builder.AppendFormat(" where id in ({0})", productlistid);
            return DbHelperSQL.Query(builder.ToString());
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM wx_shop_cart ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool RemoveCartInfo(int wid, string openid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from wx_shop_cart ");
            builder.Append(" where wid=@wid and openid=@openid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = openid;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool RemoveCartInfoByUserID(int wid, int userid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from wx_shop_cart ");
            builder.Append(" where wid=@wid and userid=@userid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@userid", SqlDbType.Int, 4) };
            cmdParms[0].Value = wid;
            cmdParms[1].Value = userid;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool Update(MxWeiXinPF.Model.wx_shop_cart model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update wx_shop_cart set ");
            builder.Append("openid=@openid,");
            builder.Append("wid=@wid,");
            builder.Append("skuId=@skuId,");
            builder.Append("skuInfo=@skuInfo,");
            builder.Append("totPrice=@totPrice,");
            builder.Append("productNum=@productNum ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@skuId", SqlDbType.Int, 4), new SqlParameter("@skuInfo", SqlDbType.VarChar, 500), new SqlParameter("@totPrice", SqlDbType.Float, 8), new SqlParameter("@productNum", SqlDbType.Int, 4), new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.openid;
            cmdParms[1].Value = model.wid;
            cmdParms[2].Value = model.skuId;
            cmdParms[3].Value = model.skuInfo;
            cmdParms[4].Value = model.totPrice;
            cmdParms[5].Value = model.productNum;
            cmdParms[6].Value = model.id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool UpdateNum(int cartId, int newNum)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update wx_shop_cart set ");
            builder.Append("productNum=@productNum, totprice=(totprice/productNum)*@productNum");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@productNum", SqlDbType.Int, 4), new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = newNum;
            cmdParms[1].Value = cartId;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

